package com.sunnystudio.mydiary.data;

import java.util.ArrayList;
import java.util.List;

import com.airbiquity.android.fleet.icsobjs.IcsConstants;
import com.airbiquity.aqlog.AqLog;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DBInstance {
	private static final String TAG = "SFSP_DBInstance";
	
	private static DBInstance minstance;
	private DBManager mgr;
	private SQLiteDatabase db;
	
	private DBInstance(Context _context) {
		mgr = new DBManager(_context);
		open();
	}
	
	public static DBInstance getInstance(Context _context) {
		if (minstance == null) {
			minstance = new DBInstance(_context);
		}
		
		return minstance;
	}
	
	public void open() {
		Log.d(TAG, "Database open...");
		if (db == null || !db.isOpen()) db = mgr.open();
	}
	
	public void close() {
		Log.d(TAG, "Database closed...");
		if (db != null) db.close();
	}
	
	public void rebuild()
	{
		mgr.rebuild();
	}

	/**
	 * 
	 * Get message list
	 * 
	 * @return List<ObjMessage>
	 */
	public List<ObjMessage> selectMessage(String driverId)
    {
        List<ObjMessage> msgs = new ArrayList<ObjMessage>();
        ObjMessage msg;
        
        String where = "message_to=? or message_to is null or message_to_type=" + IcsConstants.ID_TYPE_VEHICLE;
        String[] whereValue = {driverId};
		
		Cursor cursor = db.query(DBConstants.TABLE_MESSAGE, null, where, whereValue, null, null,  " _id desc");
        
        while (cursor.moveToNext()) {
        	msg = new ObjMessage();
        	msg.setId(cursor.getInt(0));
	    	
        	msg.setMessage_id(cursor.getLong(1));
        	msg.setParent_message_id(cursor.getLong(2));
	    	msg.setIs_read(cursor.getInt(3));
	    	msg.setIs_require_response(cursor.getInt(4));
	    	
	    	msg.setPriority(cursor.getInt(5));
	    	msg.setContent_id(cursor.getInt(6));
	    	msg.setLanguage(cursor.getString(7));
	    	msg.setEncoding(cursor.getInt(8));
	    	
	    	msg.setMessage_from_type(cursor.getInt(9));
	    	msg.setMessage_from(cursor.getString(10));
	    	msg.setMessage_to_type(cursor.getInt(11));
	    	msg.setMessage_to(cursor.getString(12));
	    	
	    	msg.setTime(cursor.getLong(13));
	    	msg.setSubject(cursor.getString(14));
	    	msg.setBody(cursor.getString(15));
	    	
	    	msgs.add(msg);
    	}
        
    	cursor.close();
    	
        return msgs;
    }
	
	/**
	 * 
	 * Get message by ID
	 * 
	 * @return ObjMessage
	 */
	public ObjMessage getMessage(long id, String where)
    {
        ObjMessage msg = new ObjMessage();
		
        String[] whereValue = {Long.toString(id)};
        
		Cursor cursor = db.query(DBConstants.TABLE_MESSAGE, null, where, whereValue, null, null,  " _id desc");
        
        while (cursor.moveToNext()) {
        	msg.setId(cursor.getInt(0));
	    	
        	msg.setMessage_id(cursor.getLong(1));
        	msg.setParent_message_id(cursor.getLong(2));
	    	msg.setIs_read(cursor.getInt(3));
	    	msg.setIs_require_response(cursor.getInt(4));
	    	
	    	msg.setPriority(cursor.getInt(5));
	    	msg.setContent_id(cursor.getInt(6));
	    	msg.setLanguage(cursor.getString(7));
	    	msg.setEncoding(cursor.getInt(8));
	    	
	    	msg.setMessage_from_type(cursor.getInt(9));
	    	msg.setMessage_from(cursor.getString(10));
	    	msg.setMessage_to_type(cursor.getInt(11));
	    	msg.setMessage_to(cursor.getString(12));
	    	
	    	msg.setTime(cursor.getLong(13));
	    	msg.setSubject(cursor.getString(14));
	    	msg.setBody(cursor.getString(15));
	    	
	    	break;
    	}
        
    	cursor.close();
        	
        return msg;
    }

	public ObjMessage getMessage(long id)
    {
        String where = "_id=?";
        return getMessage(id, where);
    }

	public boolean isMessageExistByCfmsId(long id)
    {
        String where = "message_id=?";
		ObjMessage msg = getMessage(id,where);
        return ( 0 != msg.getId() );
    }
	
	public boolean isExistReliableMsgWithMsgIdReferenceId( int msgId, long refId )
	{
		Cursor mCount= db.rawQuery("select count(*) from " + DBConstants.TABLE_RELIABLE_MESSAGE + " where message_id='" + msgId + "' and reference_id='" + refId +"'", null);
		mCount.moveToFirst();
		int count= mCount.getInt(0);
		mCount.close();		
		AqLog.getInstance().debug("isExistReliableMsgWithMsgIdReferenceId msgId,refId,count: " + Integer.toString(msgId,16) + "," + 
									Long.toString( refId, 16 ) + "" + "," +  count );
		return ( count > 0 );
	}
	
	/**
	 * 
	 * Add message into database
	 * 
	 * @param List<ObjMessage> msgs: List of message
	 * @return long id: ID of insert row
	 */
	public long addMessage(List<ObjMessage> msgs) {
		long id = 0;
		
		ContentValues cv;
		
		db.beginTransaction();  
        try {
            for (ObjMessage msg : msgs) {  
            	if (msg.isValid()) {
            		cv = new ContentValues(); 
            		cv.put("message_id", msg.getMessage_id());
            		cv.put("parent_message_id", msg.getParent_message_id());
	            	cv.put("is_read", msg.getIs_read());
	            	cv.put("is_require_response", msg.getIs_require_response());
	            	cv.put("priority", msg.getPriority());
	            	
	            	cv.put("content_id", msg.getContent_id());
	            	cv.put("language", msg.getLanguage());
	            	cv.put("encoding", msg.getEncoding());
	            	cv.put("message_from_type", msg.getMessage_from_type());
	            	
	            	cv.put("message_from", msg.getMessage_from());
	            	cv.put("message_to_type", msg.getMessage_to_type());
	            	cv.put("message_to", msg.getMessage_to());
	            	cv.put("time", msg.getTime());
	            	
	            	cv.put("subject", msg.getSubject());
	            	cv.put("body", msg.getBody());
	            	
	            	id = db.insert(DBConstants.TABLE_MESSAGE, null, cv);
            	}
            }  
            db.setTransactionSuccessful(); 
        } finally {  
            db.endTransaction();
        }  
		
		return id;
	}
	
	/**
	 * 
	 * Update message
	 * 
	 * @param ObjMessage msg
	 * @return int rows: The number of rows affected 
	 */
	public int updateMessage(ObjMessage msg) {
		int rows = 0;
		if (msg.getId() > 0 && msg.isValid()) {
			String where = "_id=?";
	        String[] whereValue = {Integer.toString(msg.getId())};
			
			ContentValues cv = new ContentValues(); 
			
			cv.put("message_id", msg.getMessage_id());
			cv.put("parent_message_id", msg.getParent_message_id());
        	cv.put("is_read", msg.getIs_read());
        	cv.put("is_require_response", msg.getIs_require_response());
        	cv.put("priority", msg.getPriority());
        	
        	cv.put("content_id", msg.getContent_id());
        	cv.put("language", msg.getLanguage());
        	cv.put("encoding", msg.getEncoding());
        	cv.put("message_from_type", msg.getMessage_from_type());
        	
        	cv.put("message_from", msg.getMessage_from());
        	cv.put("message_to_type", msg.getMessage_to_type());
        	cv.put("message_to", msg.getMessage_to());
        	cv.put("time", msg.getTime());
        	
        	cv.put("subject", msg.getSubject());
        	cv.put("body", msg.getBody());
	    	
	    	rows = db.update(DBConstants.TABLE_MESSAGE, cv, where, whereValue);
		}
		
		return rows;
	}
	
	/**
	 * 
	 * Delete message
	 * 
	 * @param int id: Message id
	 * @return int rows: The number of rows affected
	 */
	public int deleteMessage(int id) {
		String where = "_id=?";
        String[] whereValue = {Integer.toString(id)};
        int rows = db.delete(DBConstants.TABLE_MESSAGE, where, whereValue);
        
        return rows;
	}
	
	/**
	 * 
	 * Get message list
	 * 
	 * @return List<ObjAlert>
	 */
	public List<ObjAlert> selectAlert(String shiftId)
    {
        List<ObjAlert> alerts = new ArrayList<ObjAlert>();
        ObjAlert alert;
        
        String where = "shift_id=? ";
        String[] whereValue = {shiftId};
		
		Cursor cursor = db.query(DBConstants.TABLE_ALERT, null, where, whereValue, null, null,  " _id desc");
        
        while (cursor.moveToNext()) {
        	alert = new ObjAlert();
        	alert.setId(cursor.getInt(0));
        	alert.setCfmsId( cursor.getLong(1));
        	
        	alert.setAlert_type(cursor.getString(2));
        	alert.setTimestamp(cursor.getLong(3));
        	alert.setShift_id(cursor.getString(4));
        	alert.setOdometer(cursor.getInt(5));
	    	
        	alert.setLatitude(cursor.getFloat(6));
        	alert.setLongitude(cursor.getFloat(7));
        	alert.setDriver_id(cursor.getString(8));
        	alert.setDriver_id_type(cursor.getInt(9));
	    	
        	alert.setDuration(cursor.getFloat(10));
        	alert.setDistance_travelled(cursor.getFloat(11));
        	alert.setMax_speed(cursor.getFloat(12));
        	alert.setIs_read(cursor.getInt(13));
	    	
        	alerts.add(alert);
    	}
        
    	cursor.close();
    	
        return alerts;
    }
	
	/**
	 * 
	 * Get alert by ID
	 * 
	 * @return Alert
	 */
	public ObjAlert getAlert(long id, String where)
    {
        ObjAlert alert = new ObjAlert();
		
        String[] whereValue = {Long.toString(id)};
        
		Cursor cursor = db.query(DBConstants.TABLE_ALERT, null, where, whereValue, null, null,  " _id desc");
        
        while (cursor.moveToNext()) {
        	alert.setId(cursor.getInt(0));
        	alert.setCfmsId( cursor.getLong(1));
        	
        	alert.setAlert_type(cursor.getString(2));
        	alert.setTimestamp(cursor.getLong(3));
        	alert.setShift_id(cursor.getString(4));
        	alert.setOdometer(cursor.getInt(5));
	    	
        	alert.setLatitude(cursor.getFloat(6));
        	alert.setLongitude(cursor.getFloat(7));
        	alert.setDriver_id(cursor.getString(8));
        	alert.setDriver_id_type(cursor.getInt(9));
	    	
        	alert.setDuration(cursor.getFloat(10));
        	alert.setDistance_travelled(cursor.getFloat(11));
        	alert.setMax_speed(cursor.getFloat(12));
        	alert.setIs_read(cursor.getInt(13));
	    	
	    	break;
    	}
        
    	cursor.close();
        	
        return alert;
    }
	
	public boolean isAlertExistByCfmsId(long id)
    {
        String where = "cfms_id=?";
		ObjAlert alert = getAlert(id,where);
        return ( 0 != alert.getId() );
    }
	
	/**
	 * 
	 * Add alert into database
	 * 
	 * @param List<ObjAlert> alerts: List of alert
	 * @return long id: ID of insert row
	 */
	public long addAlert(List<ObjAlert> alerts) {
		long id = 0;
		
		ContentValues cv;
		
		db.beginTransaction();  
        try {
            for (ObjAlert alert : alerts) {  
            	if (alert.isValid()) {
            		cv = new ContentValues();
            		cv.put("cfms_id", alert.getCfmsId() );
            		
            		cv.put("alert_type", alert.getAlert_type());
        			cv.put("timestamp", alert.getTimestamp());
                	cv.put("shift_id", alert.getShift_id());
                	cv.put("odometer", alert.getOdometer());
                	
                	cv.put("latitude", alert.getLatitude());
                	cv.put("longitude", alert.getLongitude());
                	cv.put("driver_id", alert.getDriver_id());
                	cv.put("driver_id_type", alert.getDriver_id_type());
                	
                	cv.put("duration", alert.getDuration());
                	cv.put("distance_travelled", alert.getDistance_travelled());
                	cv.put("max_speed", alert.getMax_speed());
                	cv.put("is_read", DBConstants.OPTION_OFF);
                	
	            	id = db.insert(DBConstants.TABLE_ALERT, null, cv);
            	}
            }  
            db.setTransactionSuccessful(); 
        } finally {  
            db.endTransaction();
        }  
		
		return id;
	}
	
	/**
	 * 
	 * Update alert
	 * 
	 * @param ObjAlert alert
	 * @return int rows: The number of rows affected 
	 */
	public int updateAlert(ObjAlert alert) {
		int rows = 0;
		if (alert.getId() > 0 && alert.isValid()) {
			String where = "_id=?";
	        String[] whereValue = {Long.toString(alert.getId())};
			
			ContentValues cv = new ContentValues();
			cv.put("cfms_id", alert.getCfmsId());
			
			cv.put("alert_type", alert.getAlert_type());
			cv.put("timestamp", alert.getTimestamp());
        	cv.put("shift_id", alert.getShift_id());
        	cv.put("odometer", alert.getOdometer());
        	
        	cv.put("latitude", alert.getLatitude());
        	cv.put("longitude", alert.getLongitude());
        	cv.put("driver_id", alert.getDriver_id());
        	cv.put("driver_id_type", alert.getDriver_id_type());
        	
        	cv.put("duration", alert.getDuration());
        	cv.put("distance_travelled", alert.getDistance_travelled());
        	cv.put("max_speed", alert.getMax_speed());
        	cv.put("is_read", alert.getIs_read());
        	
	    	rows = db.update(DBConstants.TABLE_ALERT, cv, where, whereValue);
		}
		
		return rows;
	}
	
	/**
	 * 
	 * Delete alert
	 * 
	 * @param int id: Alert id
	 * @return int rows: The number of rows affected
	 */
	public int deleteAlert(int id) {
		String where = "_id=?";
        String[] whereValue = {Integer.toString(id)};
        int rows = db.delete(DBConstants.TABLE_ALERT, where, whereValue);
        
        return rows;
	}
	
	public long addSnapshotSummary(List<ObjSnapshotSummary> list) {
		long id = 0;
		
		ContentValues cv;
		
		db.beginTransaction();  
        try {
            for (ObjSnapshotSummary obj : list) {
            	if (obj.isValid()) {
            		cv = new ContentValues();
            		cv.put("timestamp", obj.getTimestamp());
            		//cv.put("data", new String(obj.getData()));
            		//DQ
            		cv.put("data", ObjSnapshotSummary.byteArrayToReportDataBlob( obj.getData() ) );
            		
        			cv.put("is_last_shift_period", (obj.getIsLastShiftPeriod() == true) ? 1 : 0);
                	cv.put("sequence_num", obj.getSequence_num());
                	
                	cv.put("shift_id", obj.getShift_id());
                	cv.put("vperf_message_version", obj.getVperf_message_version());
                	cv.put("cfms_trans_id", obj.getCfms_trans_id());
                	
	            	id = db.insert(DBConstants.TABLE_SNAPSHOT_SUMMARY, null, cv);
            	}
            }  
            db.setTransactionSuccessful(); 
        } finally {  
            db.endTransaction();
        }  
		
		return id;
	}
	
	public boolean isExistSnapshotSummaryWithShiftSequence(String shiftId, int seq)
	{
		Cursor mCount= db.rawQuery("select count(*) from " + DBConstants.TABLE_SNAPSHOT_SUMMARY + " where shift_id='" + shiftId + "' and sequence_num='" + seq +"'", null);
		mCount.moveToFirst();
		int count= mCount.getInt(0);
		mCount.close();		
		AqLog.getInstance().debug("isExistReliableMsgWithMsgIdReferenceId msgId,refId,count: " + shiftId + "," + 
									Long.toString( seq, 16 ) + "" + "," +  count );
		return ( count > 0 );
	}

	public List<ObjSnapshotSummary> getSnapshotSummariesWithShiftId(String shiftId) {
		List<ObjSnapshotSummary> list = new ArrayList<ObjSnapshotSummary>();
		ObjSnapshotSummary obj;
        
        String where = "shift_id=? ";
        String[] whereValue = {shiftId};
		
		Cursor cursor = db.query(DBConstants.TABLE_SNAPSHOT_SUMMARY, null, where, whereValue, null, null,  " sequence_num asc");
        
        while (cursor.moveToNext()) {
        	obj = new ObjSnapshotSummary();
        	obj.setId(cursor.getInt(0));
        	obj.setTimestamp(cursor.getLong(1));
        	
        	//String tmpData = cursor.getString(2);
        	//obj.setData((tmpData != null && !("null").equals(tmpData) && !("").equals(tmpData)) ? tmpData.getBytes() : new byte[0]);
        	
        	//DQ
        	String tmpData = cursor.getString(2);
        	obj.setData( ObjSnapshotSummary.reportDataBlobToByteArray(tmpData) );
        	
        	obj.setIsLastShiftPeriod(cursor.getInt(3) == DBConstants.OPTION_ON);
        	obj.setSequence_num(cursor.getInt(4));
        	obj.setShift_id(cursor.getString(5));
	    	
        	obj.setVperf_message_version(cursor.getInt(6));
        	obj.setCfms_trans_id(cursor.getLong(7));
	    	
        	list.add(obj);
    	}
        
    	cursor.close();
    	
        return list;
	}
	
	public int deleteOldSnapshotSummaries(long timestamp) {
		String where = "timestamp<?";
        String[] whereValue = {Long.toString(timestamp)};
        int rows = db.delete(DBConstants.TABLE_SNAPSHOT_SUMMARY, where, whereValue);
        
        return rows;
	}
	
	public long addShiftAbstract(List<ObjShiftAbstract> list) {
		long id = 0;
		
		ContentValues cv;
		
		db.beginTransaction();  
        try {
            for (ObjShiftAbstract obj : list) {
            	if (obj.isValid()) {
            		
            		// Delete old row with current shiftId if it exists
            		// This will occur if record exists on remote, but meta-data didn't
            		// match.
            		this.deleteShiftAbstract( obj.getShift_id());
            		
            		cv = new ContentValues();
            		cv.put("shift_id", obj.getShift_id());
            		cv.put("logon_time", obj.getLogon_time());
        			cv.put("logoff_time", obj.getLogoff_time());
                	cv.put("driver_id_type", obj.getDriver_id_type());
                	
                	cv.put("driver_id", obj.getDriver_id());
                	cv.put("total_distance_travelled", obj.getTotal_distance_travelled());
                	cv.put("total_duration", obj.getTotal_duration());
                	cv.put("idling_time", obj.getIdling_time());
                	
                	cv.put("over_rev_distance", obj.getOver_rev_distance());
                	cv.put("harsh_throttle_distance", obj.getHarsh_throttle_distance());
                	cv.put("braking_distance", obj.getBraking_distance());
                	cv.put("cruise_control_distance", obj.getCruise_control_distance());
                	
                	cv.put("coasting_distance", obj.getCoasting_distance());
                	
                	cv.put("harsh_cold_drv_distance", obj.getHarsh_cold_drv_distance());
                	cv.put("eco_drive_distance", obj.getEco_drive_distance());
                	cv.put("harsh_acc_distance", obj.getHarsh_acc_distance());
                	cv.put("harsh_brake_distance", obj.getHarsh_brake_distance());
                	cv.put("obu_id", obj.getObuId() );
                	cv.put("crc", obj.getCrc() );
                	
	            	id = db.insert(DBConstants.TABLE_SHIFT_ABSTRACT, null, cv);
            	}
            }  
            db.setTransactionSuccessful(); 
        } catch( Exception e ) {
        	Log.d(TAG, "error inserting into shift abstracts", e );
        } finally {  
            db.endTransaction();
        }  
		
		return id;
	}
	
	public List<ObjShiftAbstract> getRecentShiftAbstract(float distance, String driverId, int num) {
		List<ObjShiftAbstract> list = new ArrayList<ObjShiftAbstract>();
		ObjShiftAbstract obj;
        
        String where = "total_distance_travelled>? and driver_id=? ";
        String[] whereValue = {Float.toString(distance), driverId};
		
		Cursor cursor = db.query(DBConstants.TABLE_SHIFT_ABSTRACT, null, where, whereValue, null, null, " logon_time desc, _id desc ", Integer.toString(num));
        
        while (cursor.moveToNext()) {
        	obj = new ObjShiftAbstract();
        	obj.setId(cursor.getInt(0));
        	
        	obj.setShift_id(cursor.getString(1));
        	obj.setLogon_time(cursor.getLong(2));
        	obj.setLogoff_time(cursor.getLong(3));
        	obj.setDriver_id_type(cursor.getInt(4));
        	
        	obj.setDriver_id(cursor.getString(5));
	    	obj.setTotal_distance_travelled(cursor.getFloat(6));
        	obj.setTotal_duration(cursor.getInt(7));
        	obj.setIdling_time(cursor.getInt(8));
        	
        	obj.setOver_rev_distance(cursor.getFloat(9));
        	obj.setHarsh_throttle_distance(cursor.getFloat(10));
        	obj.setBraking_distance(cursor.getFloat(11));
        	obj.setCruise_control_distance(cursor.getFloat(12));
        	
        	obj.setCoasting_distance(cursor.getFloat(13));
        	
        	obj.setHarsh_cold_drv_distance(cursor.getFloat(14));
        	obj.setEco_drive_distance(cursor.getFloat(15));
        	obj.setHarsh_acc_distance(cursor.getFloat(16));
        	obj.setHarsh_brake_distance(cursor.getFloat(17));
        	obj.setObuId( cursor.getInt( 18 ));
        	obj.setCrc( cursor.getLong( 19 ));
        	list.add(obj);
    	}
        
    	cursor.close();
    	
        return list;
	}
	
	public int deleteShiftAbstract(long timestamp) {
		String where = "logoff_time<?";
        String[] whereValue = {Long.toString(timestamp)};
        int rows = db.delete(DBConstants.TABLE_SHIFT_ABSTRACT, where, whereValue);
        
        return rows;
	}
	
	public int deleteShiftAbstract( String shiftId )
	{
		String where = "shift_id=?";
        String[] whereValue = {shiftId};
		
		int rows = db.delete(DBConstants.TABLE_SHIFT_ABSTRACT, where, whereValue);
		return rows;
	}
	
	public long addTargetWeight(List<ObjTargetWeight> list) {
		long id = 0;
		
		ContentValues cv;
		
		db.beginTransaction();
		db.delete(DBConstants.TABLE_TARGET, null, null);
		
        try {
            for (ObjTargetWeight obj : list) {
            	if (obj.isValid()) {
            		cv = new ContentValues();
            		cv.put("version_num", obj.getVersion_num());
            		cv.put("target_braking_distance", obj.getTarget_braking_distance());
        			cv.put("target_coasting", obj.getTarget_coasting());
                	cv.put("target_cruise_ctl", obj.getTarget_cruise_ctl());
                	
                	cv.put("target_ecoband_drv", obj.getTarget_ecoband_drv());
                	cv.put("target_harsh_acceleration", obj.getTarget_harsh_acceleration());
                	cv.put("target_harsh_braking", obj.getTarget_harsh_braking());
                	cv.put("target_harsh_cold_drv", obj.getTarget_harsh_cold_drv());
                	
                	cv.put("target_harsh_throt", obj.getTarget_harsh_throt());
                	cv.put("target_idling_percent", obj.getTarget_idling_percent());
                	cv.put("target_overrev", obj.getTarget_overrev());
                	cv.put("weight_braking_distance", obj.getWeight_braking_distance());
                	
                	cv.put("weight_coasting_distance", obj.getWeight_coasting_distance());
                	cv.put("weight_cruise_ctl_distance", obj.getWeight_cruise_ctl_distance());
                	cv.put("weight_eco_drive_distance", obj.getWeight_eco_drive_distance());
                	cv.put("weight_harsh_acc_distance", obj.getWeight_harsh_acc_distance());
                	
                	cv.put("weight_harsh_brake_distance", obj.getWeight_harsh_brake_distance());
                	cv.put("weight_harsh_cold_drv_distance", obj.getWeight_harsh_cold_drv_distance());
                	cv.put("weight_harsh_throt_distance", obj.getWeight_harsh_throt_distance());
                	cv.put("weight_idling_time", obj.getWeight_idling_time());
                	
                	cv.put("weight_over_rev_distance", obj.getWeight_over_rev_distance());
                	cv.put("target_grade", obj.getTarget_grade() );
                	
	            	id = db.insert(DBConstants.TABLE_TARGET, null, cv);
	            	break;
            	}
            }  
            db.setTransactionSuccessful(); 
        } finally {  
            db.endTransaction();
        }  
		
		return id;
	}
	
	public ObjTargetWeight getTargetWeight() {
		ObjTargetWeight obj = new ObjTargetWeight();
		        
        String where = null;
        String[] whereValue = null;
		
		Cursor cursor = db.query(DBConstants.TABLE_TARGET, null, where, whereValue, null, null, " version_num desc, _id desc ", "1");
        
        while (cursor.moveToNext()) {
        	obj.setId(cursor.getInt(0));
        	obj.setVersion_num(cursor.getInt(1));
        	
        	obj.setTarget_braking_distance(cursor.getDouble(2));
        	obj.setTarget_coasting(cursor.getDouble(3));
        	obj.setTarget_cruise_ctl(cursor.getDouble(4));
        	obj.setTarget_ecoband_drv(cursor.getDouble(5));
        	
        	obj.setTarget_harsh_acceleration(cursor.getDouble(6));
	    	obj.setTarget_harsh_braking(cursor.getDouble(7));
        	obj.setTarget_harsh_cold_drv(cursor.getDouble(8));
        	obj.setTarget_harsh_throt(cursor.getDouble(9));
        	
        	obj.setTarget_idling_percent(cursor.getDouble(10));
        	obj.setTarget_overrev(cursor.getDouble(11));
        	obj.setWeight_braking_distance(cursor.getDouble(12));
        	obj.setWeight_coasting_distance(cursor.getDouble(13));
        	
        	obj.setWeight_cruise_ctl_distance(cursor.getDouble(14));
        	obj.setWeight_eco_drive_distance(cursor.getDouble(15));
        	obj.setWeight_harsh_acc_distance(cursor.getDouble(16));
        	obj.setWeight_harsh_brake_distance(cursor.getDouble(17));
        	
        	obj.setWeight_harsh_cold_drv_distance(cursor.getDouble(18));
        	obj.setWeight_harsh_throt_distance(cursor.getDouble(19));
        	obj.setWeight_idling_time(cursor.getDouble(20));
        	obj.setWeight_over_rev_distance(cursor.getDouble(21));
        	obj.setTarget_grade( cursor.getDouble(22) );
        	
        	break;
    	}
        
    	cursor.close();
		
		return obj;
	}
	
	public int deleteTargetWeight(int version) {
		
		String where = "version_num=?";
        String[] whereValue = {Integer.toString(version)};
        int rows = db.delete(DBConstants.TABLE_TARGET, where, whereValue);
		
		return rows;
	}
	
	public long addTips(List<ObjTips> list) {
		long id = 0;
		
		ContentValues cv;
		
		db.beginTransaction();  
        try {
            for (ObjTips obj : list) {
            	if (obj.isValid()) {
            		cv = new ContentValues();
            		cv.put("tip_number", obj.getTip_number());
            		cv.put("tip_category", obj.getTip_category());
        			cv.put("tip_level", obj.getTip_level());
                	cv.put("tip_language", obj.getTip_language());
                	
                	cv.put("tip_text", obj.getTip_text());
                	
	            	id = db.insert(DBConstants.TABLE_TIPS, null, cv);
            	}
            }  
            db.setTransactionSuccessful(); 
        } finally {  
            db.endTransaction();
        }  
		
		return id;
	}
	
	public List<ObjTips> selectTips(int category, int level, int language) {
		List<ObjTips> list = new ArrayList<ObjTips>();
		ObjTips obj;
        
        String where = " tip_category=? and tip_level=? and tip_language=? ";
        String[] whereValue = {Integer.toString(category), Integer.toString(level), Integer.toString(language)};
		
		Cursor cursor = db.query(DBConstants.TABLE_TIPS, null, where, whereValue, null, null, " _id desc ");
        
        while (cursor.moveToNext()) {
        	obj = new ObjTips();
        	obj.setId(cursor.getInt(0));
        	
        	obj.setTip_number(cursor.getLong(1));
        	obj.setTip_category(cursor.getInt(2));
        	obj.setTip_level(cursor.getInt(3));
        	obj.setTip_language(cursor.getInt(4));
        	
        	obj.setTip_text(cursor.getString(5));
        	
        	list.add(obj);
    	}
        
    	cursor.close();
    	
        return list;
	}
	
	public int deleteTips(long tipNumber) {
		String where = "tip_number=?";
        String[] whereValue = {Long.toString(tipNumber)};
        int rows = db.delete(DBConstants.TABLE_TIPS, where, whereValue);
        
        return rows;
	}
	
	public long addConfig(ObjConfig config) {
		long id = 0;
		
		if (config.isValid()) {
			ContentValues cv = new ContentValues(); 
			
        	cv.put("driver_id", config.getDriver_id());
        	cv.put("driver_type", config.getDriver_type());
        	cv.put("is_eula_accepted", config.getIs_eula_accepted());
        	cv.put("language", config.getLanguage());
        	cv.put("audio", config.getAudio());
        	
        	cv.put("show_high_msg", config.getShow_high_msg());
        	cv.put("show_high_alert", config.getShow_high_alert());
        	
        	id = db.insert(DBConstants.TABLE_CONFIG, null, cv);
    	}
		
		return id;
	}
	
	public ObjConfig getConfig(String driverId) {
		ObjConfig config = new ObjConfig();
				
        String where = "driver_id=?";
        String[] whereValue = {driverId};
        
		Cursor cursor = db.query(DBConstants.TABLE_CONFIG, null, where, whereValue, null, null, null);
        
        while (cursor.moveToNext()) {
        	config.setId(cursor.getInt(0));
        	
        	config.setDriver_id(cursor.getString(1));
        	config.setDriver_type(cursor.getInt(2));
        	config.setIs_eula_accepted(cursor.getInt(3));
        	config.setLanguage(cursor.getString(4));
        	
        	config.setAudio(cursor.getInt(5));
        	config.setShow_high_msg(cursor.getInt(6));
        	config.setShow_high_alert(cursor.getInt(7));
        	
        	break;
    	}
        
    	cursor.close();
		
		return config;
	}
	
	public int updateConfig(ObjConfig config) {
		int rows = 0;
		
		if (config.getId() > 0 && config.getDriver_id() != "" && config.isValid()) {
			String where = "_id=?";
	        String[] whereValue = {Integer.toString(config.getId())};
	        
			ContentValues cv = new ContentValues(); 
			
			cv.put("driver_id", config.getDriver_id());
			cv.put("driver_type", config.getDriver_type());
        	cv.put("is_eula_accepted", config.getIs_eula_accepted());
        	cv.put("language", config.getLanguage());
        	cv.put("audio", config.getAudio());
        	
        	cv.put("show_high_msg", config.getShow_high_msg());
        	cv.put("show_high_alert", config.getShow_high_alert());
        	
        	rows = db.update(DBConstants.TABLE_CONFIG, cv, where, whereValue);
		}
		
		return rows;
	}
	
	public int deleteConfig(int driverId) {
		String where = "driver_id=?";
        String[] whereValue = {Integer.toString(driverId)};
        int rows = db.delete(DBConstants.TABLE_CONFIG, where, whereValue);
        
        return rows;
	}
	
	public long addReliableMessage(List<ObjReliableMessage> msgs) {
		long id = 0;
		
		ContentValues cv = new ContentValues(); 
		
		db.beginTransaction();  
        try {  
            for (ObjReliableMessage msg : msgs) {  
            	if (msg.isValid()) {
	            	cv.put("transaction_id", msg.getTransaction_id());
	            	cv.put("priority", msg.getPriority());
	            	cv.put("retries", msg.getRetries());
	            	cv.put("message_data", msg.getMessage_data());
	            	cv.put("message_id", msg.getMessageId() );
	            	cv.put("reference_id", msg.getReferenceId() );
	            	
	            	id = db.insert(DBConstants.TABLE_RELIABLE_MESSAGE, null, cv);
            	}
            }  
            db.setTransactionSuccessful(); 
        } finally {  
            db.endTransaction();
        }  
		
		return id;
	}
	
	public List<ObjReliableMessage> selectReliableMessage() {
		List<ObjReliableMessage> msgs = new ArrayList<ObjReliableMessage>();
		ObjReliableMessage msg;
		
		Cursor cursor = db.query(DBConstants.TABLE_RELIABLE_MESSAGE, null, null, null, null, null,  " priority desc, transaction_id asc");
        
        while (cursor.moveToNext()) {
        	msg = new ObjReliableMessage();
        	msg.setTransaction_id(cursor.getLong(1));
        	msg.setPriority(cursor.getInt(2));
        	msg.setRetries(cursor.getInt(3));
        	msg.setMessage_data(cursor.getString(4));
	    	
	    	msgs.add(msg);
    	}
        
    	cursor.close();
        	
        return msgs;
	}
	
	public ObjReliableMessage getReliableMessage(long transactionId) {
		ObjReliableMessage msg = new ObjReliableMessage();
		
        String where = "transaction_id=?";
        String[] whereValue = {Long.toString(transactionId)};
        
		Cursor cursor = db.query(DBConstants.TABLE_RELIABLE_MESSAGE, null, where, whereValue, null, null,  null);
        
        while (cursor.moveToNext()) {
        	msg.setTransaction_id(cursor.getLong(1));
        	msg.setPriority(cursor.getInt(2));
        	msg.setRetries(cursor.getInt(3));
        	msg.setMessage_data(cursor.getString(4));
        	
        	break;
    	}
        
    	cursor.close();
    	
		return msg;
	}
	
	public int updateReliableMessage(ObjReliableMessage msg) {
		int rows = 0;
		
		if (msg.getTransaction_id() >= 0 && msg.isValid()) {
			String where = "transaction_id=?";
	        String[] whereValue = {Long.toString(msg.getTransaction_id())};
	        
			ContentValues cv = new ContentValues(); 
			
			cv.put("transaction_id", msg.getTransaction_id());
        	cv.put("priority", msg.getPriority());
        	cv.put("retries", msg.getRetries());
        	cv.put("message_data", msg.getMessage_data());
        	        	
        	rows = db.update(DBConstants.TABLE_RELIABLE_MESSAGE, cv, where, whereValue);
		}
		
		return rows;
	}
	
	public int deleteReliableMessage(long transactionId) {
		String where = "transaction_id=?";
        String[] whereValue = {Long.toString(transactionId)};
        int rows = db.delete(DBConstants.TABLE_RELIABLE_MESSAGE, where, whereValue);
        
        return rows;
	}
	
/*
	// TODO, may not need
	public long addReplywithOptions(List<ObjReplywith> opts) {
		long id = 0;
		
		ContentValues cv = new ContentValues(); 
		
		db.beginTransaction();  
        try {  
            for (ObjReplywith opt : opts) {  
            	if (opt.isValid()) {
	            	cv.put("language", opt.getLanguage());
	            	cv.put("name", opt.getName());
	            	cv.put("field_order", opt.getOrder());
	            	
	            	id = db.insert(DBConstants.TABLE_REPLYWITH, null, cv);
            	}
            }  
            db.setTransactionSuccessful(); 
        } finally {  
            db.endTransaction();
        }  
		
		return id;
	}
	
	// TODO, may not need
	public List<ObjReplywith> getReplywithOptions() {
		ArrayList<ObjReplywith> opts = new ArrayList<ObjReplywith>();
		ObjReplywith opt;
		
		Cursor cursor = db.query(DBConstants.TABLE_REPLYWITH, null, null, null, null, null,  " order asc");
        
        while (cursor.moveToNext()) {
        	opt = new ObjReplywith();
        	opt.setId(cursor.getInt(0));
        	opt.setLanguage(cursor.getString(1));
        	opt.setName(cursor.getString(2));
        	opt.setOrder(cursor.getInt(3));
        	
        	opts.add(opt);
    	}
        
    	cursor.close();
        	
        return opts;
	}
	
	// TODO, may not need?
	public void displayObjReliableMessage() {
		Cursor cursor = db.query(DBConstants.TABLE_RELIABLE_MESSAGE, null, null, null, null, null,  " message_id asc, reference_id asc");

		AqLog.getInstance().debug("_id, transaction_id, priority, retries, message_data, message_id, reference_id" );
        while (cursor.moveToNext()) {

        	String row = cursor.getInt(0) + ","
        			   + cursor.getInt(1) + ","
        			   + cursor.getInt(2) + ","
        			   + cursor.getInt(3) + ","
        			   + cursor.getString(4) + ","
        			   + cursor.getInt(5) + ","
        			   + cursor.getLong(6);
        	
        	AqLog.getInstance().debug(row);
    	}
    	cursor.close();
	}
*/
	
}
